package vlms.dao;
import java.sql.*;
import java.util.*;

import vlms.bean.SimpleCustomer;
import vlms.exception.SimplecustomerException;

public class SimplecustomerDAO {

	static HashMap<String, SimpleCustomer> primaryList = new HashMap<String, SimpleCustomer>();
	static HashMap<Integer, SimpleCustomer> personIdList = new HashMap<Integer, SimpleCustomer>();
	static long newMembershipNo = 10000001;
	
	//Static Method Starts
	private static void initializeList(Connection con) {
		try {
			PreparedStatement stmt = null;
			ResultSet rs = null;

			String SQL_STATEMENT = "Select * from simplecustomer";
			stmt = con.prepareStatement(SQL_STATEMENT);
			rs = stmt.executeQuery();
			while (rs.next()) {
				SimpleCustomer dto = new SimpleCustomer();
				staticPopulateVO(dto, rs);
				primaryList.put(dto.getMembershipno(), dto);
				personIdList.put(new Integer(dto.getPersonid()), dto);
			}			
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	private static void initializeMembershipNo(Connection con){
		try {
			PreparedStatement stmt = null;
			ResultSet rs = null;
			String sMembershipNo = null;

			String SQL_STATEMENT = "Select membershipNo from SimpleCustomer where personId IN (Select max(personId) from SimpleCustomer)";
			stmt = con.prepareStatement(SQL_STATEMENT);
			rs = stmt.executeQuery();
			while (rs.next()) {
				sMembershipNo = rs.getString("membershipNo");				
			}
			
			if(sMembershipNo != null) {
				String[] temp = sMembershipNo.split("-");
				sMembershipNo = "";
				for(int i=0;i<temp.length;i++) {
					sMembershipNo += temp[i];
				}
				newMembershipNo = Long.parseLong(sMembershipNo) + 1;
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	static {
		Connection con = null;
		try {
			con = DriverManager.getConnection("jdbc:user_mysql:");
			initializeList(con);
			initializeMembershipNo(con);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				con.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	private static void staticPopulateVO(SimpleCustomer dto, ResultSet rs)
			throws SQLException {
		dto.setMembershipno(rs.getString("membershipNo"));
		dto.setOutstandingmovie(rs.getInt("outStandingMovies"));
		dto.setPersonid(rs.getInt("personId"));
		dto.setBalance(rs.getInt("balance"));
	}

	protected SimpleCustomer[] fetchMultiResults(ResultSet rs)
			throws SQLException {
		Collection<SimpleCustomer> resultList = new ArrayList<SimpleCustomer>();
		while (rs.next()) {
			SimpleCustomer dto = new SimpleCustomer();
			populateVO(dto, rs);
			resultList.add(dto);
		}
		SimpleCustomer ret[] = new SimpleCustomer[resultList.size()];
		resultList.toArray(ret);
		return ret;
	}
	
	private HashMap<String, Object> findUpdatedFields(SimpleCustomer newObj, SimpleCustomer listObj)
	{
		HashMap<String, Object> result = new HashMap<String, Object>();
		
		if (newObj.getBalance()!= listObj.getBalance())
		{
			result.put("balance", newObj.getBalance());
		}
		if (newObj.getOutstandingmovie() != listObj.getOutstandingmovie())
		{
			result.put("outstandingmovies", newObj.getOutstandingmovie());
		}
		
		return result;
	}
		//Static Method Ends

	public int delete(String memberShipNo, Connection con)
			throws SimplecustomerException, SQLException {
		PreparedStatement ps = null;
		SimpleCustomer sc = findByPrimaryKey(memberShipNo, con);
		ps = con.prepareStatement("delete from  SIMPLECUSTOMER where membershipNo = ?");
		ps.setString(1, memberShipNo);
		int result = ps.executeUpdate();

		if (result <= 0) {
			throw new SimplecustomerException("No simple customer found with "
					+ "membership number : " + memberShipNo);
		}
		primaryList.remove(memberShipNo);
		personIdList.remove(sc.getPersonid());
		return result;
	}

	public int update(String memberShipNo, SimpleCustomer simplecustomer,
			Connection con) throws SimplecustomerException, SQLException {
		PreparedStatement ps = null;
		SimpleCustomer sc = primaryList.get(memberShipNo);
		int result = 0;
		String sqlQuery;		
		String fieldsValue = null;

		if (sc != null)
		{
			HashMap<String, Object> updated = findUpdatedFields(simplecustomer, 
																sc);
			Set<String> keys = updated.keySet();
			Iterator<String> it = keys.iterator();
			
			System.out.println("Keys : " + keys.toString());
			
			while(it.hasNext()) {
				String value = it.next();
				if(fieldsValue != null) 
					fieldsValue += "," + value + " = '" + updated.get(value) + "'";
				else
					fieldsValue = value + " = '" + updated.get(value) + "'";
			}
			
			System.out.println("Fields Value " + fieldsValue + "  Membershipno: " + simplecustomer.getMembershipno());
			
			if(fieldsValue != null) {
				sqlQuery = "update simplecustomer set " + fieldsValue + " where membershipno = ?";
				
				ps = con.prepareStatement(sqlQuery);
				ps.setString(1, simplecustomer.getMembershipno());
				result = ps.executeUpdate();
				
				if(result > 0) {
					sqlQuery = "select * from simplecustomer where membershipno = ?";
					ps = con.prepareStatement(sqlQuery);
					ps.setString(1, simplecustomer.getMembershipno());
					ResultSet rs = ps.executeQuery();
					
					
						
						System.out.println("In rs.next");
						
						sc = fetchSingleResult(rs);
						System.out.println("In rs.next 2");
						primaryList.put(sc.getMembershipno(), sc);
						System.out.println("In rs.next 3");
						personIdList.put(sc.getPersonid(), sc);
						System.out.println("In rs.next 4");
					
					
					return result;
				}
			}
			else
			{
				return result;
			}
			
			
			if (result <= 0)
			{
				throw new SimplecustomerException("Failed to update the "
						+ "simplecustomer with membership number :" + memberShipNo);
			}

		}
		else
		{
			sqlQuery ="update SIMPLECUSTOMER set " +
					  "OUTSTANDINGMOVIES = ? , PERSONID = ? , " +
					  "BALANCE = ?  where membershipNo = ?";
			ps = con.prepareStatement(sqlQuery);
			ps.setInt(1, simplecustomer.getOutstandingmovie());
			ps.setInt(2, simplecustomer.getPersonid());
			ps.setFloat(3, simplecustomer.getBalance());
			ps.setString(4, memberShipNo);

			result = ps.executeUpdate();

			if (result <= 0) {
				throw new SimplecustomerException("Failed to update the "
						+ "simplecustomer with membership number :" + memberShipNo);
			}
			primaryList.remove(memberShipNo);
			personIdList.remove(simplecustomer.getPersonid());
			primaryList.put(memberShipNo, simplecustomer);
			personIdList.put(simplecustomer.getPersonid(), simplecustomer);
		}
		return result;
	}

	public int insert(SimpleCustomer simplecustomer, Connection con)
			throws SimplecustomerException, SQLException {

		PreparedStatement ps = null;
		
		String temp = Long.toString(newMembershipNo);
		String membershipNo = temp.substring(0, 3) + "-" + temp.substring(3, 5) + "-" + temp.substring(5, 8);
		
		simplecustomer.setMembershipno(membershipNo);
		
		ps = con.prepareStatement("insert into SIMPLECUSTOMER(MEMBERSHIPNO, OUTSTANDINGMOVIES, PERSONID, BALANCE) values (?,?, ?, ?)");
		ps.setString(1, simplecustomer.getMembershipno());		
		ps.setInt(2, simplecustomer.getOutstandingmovie());
		ps.setInt(3, simplecustomer.getPersonid());
		ps.setFloat(4, simplecustomer.getBalance());

		int result = (ps.executeUpdate());

		if (result <= 0) {
			throw new SimplecustomerException(
					"Failed to insert the simple customer record with membership number: "
							+ simplecustomer.getMembershipno());
		}
		primaryList.put(simplecustomer.getMembershipno(), simplecustomer);
		personIdList.put(simplecustomer.getPersonid(), simplecustomer);
		
		return result;
	}

	public SimpleCustomer findByPrimaryKey(String membershipno, Connection con)
			throws SimplecustomerException,SQLException {
		
		SimpleCustomer sc = primaryList.get(membershipno);
		
		if (sc != null)
		{
			return sc;
		}
		else
		{
			PreparedStatement stmt = null;
			ResultSet rs = null;
			final String SQLSTATEMENT = "Select membershipNo, outStandingMovies, personId, balance from simplecustomer where membershipNo = ?";
			stmt = con.prepareStatement(SQLSTATEMENT);
			stmt.setString(1, membershipno);
			rs = stmt.executeQuery();
			sc = fetchSingleResult(rs);			
		}
		if (sc == null)
		{
			throw new SimplecustomerException("Simple Customer not found with membership number : "+ membershipno);
		}
		else
		{
			primaryList.put(membershipno, sc);
			personIdList.put(sc.getPersonid(), sc);
		}
		return sc;
	}

	public SimpleCustomer findByPersonid(int personid, Connection con)
			throws SimplecustomerException,SQLException {
		
		SimpleCustomer sc = personIdList.get(personid);
		System.out.println("Simple Customer: " + sc);
		if (sc != null)
		{
			return sc;
		}
		PreparedStatement stmt = null;
		ResultSet rs = null;
		String SQL_STATEMENT = "Select membershipNo, outStandingMovies, personId, balance from simplecustomer where personId = ? order by personId";
		stmt = con.prepareStatement(SQL_STATEMENT);
		stmt.setInt(1, personid);
		rs = stmt.executeQuery();
		sc = fetchSingleResult(rs);
		
		if (sc == null)
		{
			throw new SimplecustomerException("Cannot find simple customer in the system.");
		}
		else
		{
			personIdList.put(personid, sc);
		}
		return sc;
	}

	public SimpleCustomer[] findAll(Connection con)
		throws SimplecustomerException,	SQLException {
		
		int size = primaryList.size();
		SimpleCustomer[] sc = primaryList.values().toArray(new SimpleCustomer[size]);

		if (sc.length <= 0)
		{
			throw new SimplecustomerException("No Simple customer found in the system.");
		}
		
		return sc;
//		PreparedStatement stmt = null;
//		ResultSet rs = null;
//		String SQL_STATEMENT = "Select membershipNo, outStandingMovies, personId, balance from simplecustomer";
//		stmt = con.prepareStatement(SQL_STATEMENT);
//		rs = stmt.executeQuery();
//		return fetchMultiResults(rs);
	}

	public SimpleCustomer[] findExecutingUserSelect(String selectStatement,
			Object[] sqlParams, Connection con) throws SimplecustomerException {
		PreparedStatement stmt = null;
		ResultSet rs = null;
		final String SQL_STATEMENT = selectStatement;
		try {
			stmt = con.prepareStatement(SQL_STATEMENT);
			for (int i = 0; i < sqlParams.length; i++) {
				stmt.setObject(i + 1, sqlParams[i]);
			}
			rs = stmt.executeQuery();
			return fetchMultiResults(rs);
		} catch (SQLException sqle) {
			throw new SimplecustomerException(sqle);
		} catch (Exception e) {
			throw new SimplecustomerException(e);
		} finally {
		}
	}

	public SimpleCustomer[] findExecutingUserWhere(String whereClause,
			Object[] sqlParams, Connection con) throws SimplecustomerException {
		PreparedStatement stmt = null;
		ResultSet rs = null;
		String SQL_SELECT = "Select membershipNo, outStandingMovies, personId, balance from simplecustomer";
		final String SQL_STATEMENT = SQL_SELECT + " where " + whereClause;
		try {
			stmt = con.prepareStatement(SQL_STATEMENT);
			for (int i = 0; i < sqlParams.length; i++) {
				stmt.setObject(i + 1, sqlParams[i]);
			}
			rs = stmt.executeQuery();
 			return fetchMultiResults(rs);
		} catch (SQLException sqle) {
			throw new SimplecustomerException(sqle);
		} catch (Exception e) {
			throw new SimplecustomerException(e);
		} finally {
		}
	}

	protected SimpleCustomer fetchSingleResult(ResultSet rs)
			throws SQLException {
		if (rs.next()) {
			SimpleCustomer dto = new SimpleCustomer();
			populateVO(dto, rs);
			return dto;
		} else {
			return null;
		}
	}

	protected void populateVO(SimpleCustomer dto, ResultSet rs)
			throws SQLException {
		dto.setMembershipno(rs.getString("membershipNo"));
		dto.setOutstandingmovie(rs.getInt("outStandingMovies"));
		dto.setPersonid(rs.getInt("personId"));
		dto.setBalance(rs.getFloat("balance"));
	}

	
}
